package month.statistic;

import entity.month.StatMonth;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import utility.database.SqlServerAccess;
import utility.time.ProcessTime;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.HashMap;


/**
 * 处理月统计数据表
 * @author lit
 * @date  2018/3/26.
 */
public class ProcessStatMonth {
    /**
     * 数据连接
     */
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private SqlServerAccess mssql = null;

    private Logger logger = null;

    private boolean initMSSQL(){
        if( connection == null ){
            try {
                connection = SqlServerAccess.getInstance();
                connection.setAutoCommit(false);
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

    private void finishMSSQL(){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void processStatMonth(String[] statTime){
        logger = LoggerFactory.getLogger(ProcessStatMonth.class);
        logger.info("开始处理月统计数据表");
        StatMonth statMonth = new StatMonth();
        int year =  ProcessTime.getYear(statTime[1]);
        int month = ProcessTime.getMonth(statTime[1]);
        statMonth.setStatYear(year);
        statMonth.setStatMonth(month);
        logger.info("即将清除月统计数据表已有{}年{}月数据",year,month);
        clearStatMonth(year,month);

        logger.info("开始获取车辆总数");
        int vehicleTotalNum = getVehicleTotalNum(statTime);
        if(vehicleTotalNum > 0){
            logger.info("共有{}辆车",vehicleTotalNum);
            statMonth.setVehicleTotalNum(vehicleTotalNum);
        }
        logger.info("获取最大在线车辆数，最大在线时间");
        getMaxOnlineIndex(statTime,statMonth);

        logger.info("获取最大点火车辆数，最大点火时间");
        getMaxFireIndex(statTime,statMonth);

        logger.info("获取平均速度");
        Double avgSpeed = getAvgSpeed(statTime);
        if(avgSpeed > 0){
            statMonth.setAvgSpeed(avgSpeed);
        }

        logger.info("获取注册车辆数");
        int regVehicleNum = getRegVehicleNum(statTime);
        if(regVehicleNum > 0){
            statMonth.setRegTotalCarNum(regVehicleNum);
        }

        logger.info("月统计数据插入数据库");
        addStatMonthData(statTime,statMonth);
        logger.info("月统计操作结束");
        finishMSSQL();
    }

    private void clearStatMonth(int year, int month) {
        if(initMSSQL()){
            String sql = "delete FROM IDBox_Master.dbo.stat_month where StatYear = ? and StatMonth = ?";
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,year);
                preparedStatement.setInt(2,month);
                int num = preparedStatement.executeUpdate();
                logger.info("删除月统计数据{}条",num);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 1.获取车辆总数
     * @param statTime
     * @return
     */
    private int getVehicleTotalNum(String[] statTime) {
        if(initMSSQL()){
            String sql = "SELECT COUNT(DISTINCT VID) as VehicleNum FROM rtm_trip Where TripStartTime > =? AND TripStartTime < ?";
            ResultSet resultSet = null;

            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,statTime[1]);
                preparedStatement.setString(2,statTime[2]);

                if(preparedStatement != null){
                    resultSet = preparedStatement.executeQuery();
                    if(resultSet.next()){
                        int vehicleTotalNum = resultSet.getInt("VehicleNum");
                        return vehicleTotalNum;
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * 2.获取在线相关指标，最大在线车辆数，最大在线时间
     * @param statTime
     * @param statMonth
     */
    private void getMaxOnlineIndex(String[] statTime, StatMonth statMonth) {
        if(initMSSQL()){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
            SimpleDateFormat sdfLocal = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sql = "SELECT TripStartTime,TripEndTime FROM rtm_trip WHERE TripStartTime >= ? AND TripStartTime < ?";

            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,statTime[1]);
                preparedStatement.setString(2,statTime[2]);
                if(preparedStatement != null){
                    HashMap<String, Integer> onlineMap = new HashMap<>();
                    ResultSet resultSet = preparedStatement.executeQuery();
                    while(resultSet.next()){
                        Timestamp tripStartTime = resultSet.getTimestamp("TripStartTime");
                        Timestamp tripEndTime = resultSet.getTimestamp("TripEndTime");
                        String lowerBound = sdf.format(tripStartTime).substring(0, 10) + String.format("%02d", 5 * ( Integer.parseInt( sdf.format(tripStartTime).substring(10, 12) ) / 5 ) );
                        Timestamp temp = new Timestamp(sdf.parse(lowerBound).getTime());
                        while( temp.getTime() < tripEndTime.getTime() ){
                            String key = sdf.format(temp);
                            if( onlineMap.get(key) != null){
                                onlineMap.replace(key, onlineMap.get(key)+1);
                            }else{
                                onlineMap.put(key, 1);
                            }
                            //增加五分钟间隔，毫秒数
                            temp = new Timestamp(temp.getTime() + 60*1000*5);
                        }
                    }

                    //遍历获取最大在线数和对应的时间
                    int maxOnlineNum = 0;
                    String maxOnlineTime = null;
                    for(String time : onlineMap.keySet()){
                        if(onlineMap.get(time) > maxOnlineNum){
                            maxOnlineNum = onlineMap.get(time);
                            maxOnlineTime = time;
                        }
                    }
                    if(maxOnlineNum > 0){
                        statMonth.setMaxOnlineNum(maxOnlineNum);

                        String onlineTime = sdfLocal.format(sdf.parse(maxOnlineTime));
                        statMonth.setMaxOnlineTime(onlineTime);
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 3.获取点火相关指标，最大点火车辆数，最大点火时间
     * @param statTime
     * @param statMonth
     */
    private void getMaxFireIndex(String[] statTime, StatMonth statMonth) {
        if(initMSSQL()){
            String sql = "SELECT cast(concat(SUBSTRING(convert(varchar, TripStartTime, 20),1, 13),':', floor(SUBSTRING(convert(varchar, TripStartTime, 14),4, 2)/5)*5)as datetime) as period,count(1) as num FROM rtm_trip WHERE TripStartTime > =? AND TripStartTime < ? GROUP BY cast(concat(SUBSTRING(convert(varchar, TripStartTime, 20),1, 13),':', floor(SUBSTRING(convert(varchar, TripStartTime, 14),4, 2)/5)*5)as datetime) ORDER BY num desc";
            ResultSet resultSet = null;

            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,statTime[1]);
                preparedStatement.setString(2,statTime[2]);

                if(preparedStatement != null){
                    resultSet = preparedStatement.executeQuery();
                    //倒序排的，所以取第一行数据就是最大点火时间和最大点火数
                    if(resultSet.next()){
                        statMonth.setMaxFireNum(resultSet.getInt("num"));
                        statMonth.setMaxFireTime(resultSet.getString("period"));
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }

    }

    /**
     * 4.获取平均速度,五环以内车辆
     * @param statTime
     * @return
     */
    private Double getAvgSpeed(String[] statTime) {
        if(initMSSQL()){
            String sql =  "SELECT SUM(TripSpeed)/COUNT(*) as AvgSpeed FROM rtm_trip a INNER JOIN IDBox_Master.dbo.stat_month_userPortrait  b ON a.VID = b.VID WHERE a.TripStartTime >= ? and a.TripStartTime < ? and ((CONVERT (VARCHAR,a.TripStartTime,8) >='07:00:00' AND CONVERT (VARCHAR,a.TripStartTime,8) <'09:00:00') or (CONVERT (VARCHAR,a.TripStartTime,8) >='17:00:00' AND CONVERT (VARCHAR,a.TripStartTime,8) <'19:00:00')) AND b.StatMonth = ? AND b.StatYear = ? AND b.home_ring < 119";
            ResultSet resultSet = null;
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,statTime[1]);
                preparedStatement.setString(2,statTime[2]);
                preparedStatement.setInt(3,ProcessTime.getMonth(statTime[1]));
                preparedStatement.setInt(4,ProcessTime.getYear(statTime[1]));

                if(preparedStatement != null){
                    resultSet = preparedStatement.executeQuery();
                    if(resultSet.next()){
                        return resultSet.getDouble("AvgSpeed");
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return 0.0;
    }

    /**
     * 5.获取注册车辆总数
     * @param statTime
     * @return
     */
    private int getRegVehicleNum(String[] statTime) {
        if(initMSSQL()){
            String sql = "select ModCarNum from [IDBox_Base].[dbo].[BJ_Area] where DistID=1000";
            ResultSet resultSet = null;
            try{
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){
                    int regVehicleNum = resultSet.getInt("ModCarNum");
                    return regVehicleNum;
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * 6.月统计数据添加插入到数据库
     * @param statTime
     * @param statMonth
     */
    private void addStatMonthData(String[] statTime, StatMonth statMonth) {
        if(initMSSQL()){
            String sql = "insert into IDBox_Master.dbo.stat_month (StatYear,StatMonth,VehicleTotalNum,MaxOnlineNum,MaxOnlineTime,MaxFireNum,MaxFireTime,AvgSpeed,RegTotalCarNum) values (?,?,?,?,?,?,?,?,?)";
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,ProcessTime.getYear(statTime[1]));
                preparedStatement.setInt(2,ProcessTime.getMonth(statTime[1]));
                preparedStatement.setInt(3,statMonth.getVehicleTotalNum());
                preparedStatement.setInt(4,statMonth.getMaxOnlineNum());
                preparedStatement.setString(5,statMonth.getMaxOnlineTime());
                preparedStatement.setInt(6,statMonth.getMaxFireNum());
                preparedStatement.setString(7,statMonth.getMaxFireTime());
                preparedStatement.setDouble(8,statMonth.getAvgSpeed());
                preparedStatement.setInt(9,statMonth.getRegTotalCarNum());

                if(preparedStatement != null){
                    int num = preparedStatement.executeUpdate();
                    connection.commit();
                    logger.info("月统计插入{}条数据",num);
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }
}
